{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2cb24bb2-ed6d-45f8-9cf5-96afb7074b1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "csv_data=pd.read_csv('合并人员住址信息.csv',encoding='utf8',dtype='str')\n",
    "csv_data.to_sql('合并人员住址信息',db_engine,if_exists='replace',index=False)\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bd9111bd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "csv_data2=pd.read_csv('合并人员信息2.csv',encoding='gbk')\n",
    "csv_data2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0f9ea6dc",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "csv_data=pd.read_csv('合并人员住址信息.csv',encoding='gbk')\n",
    "csv_data.to_sql('合并人员住址信息',db_engine,if_exists='replace',index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5119f5a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "db_connect= db_engine.connect()\n",
    "df = pd.read_sql(\"select * from 合并交易明细信息\",db_connect)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c5be15e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "csv_data=pd.read_excel('工资汇总表.xlsx')\n",
    "csv_data['发放日期'] = pd.to_datetime(csv_data['发放日期'])\n",
    "display(csv_data)\n",
    "\n",
    "#csv_data.to_sql('工资汇总表',db_engine,if_exists='replace',index=False)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "2f3f8e18",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd;\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "db_connect= db_engine.connect()\n",
    "df1 = pd.read_sql(\"select * from 外地文创部门 ORDER BY 序号 ASC;\",db_connect)\n",
    "#df1 = pd.read_sql(\"select * from 合并交易明细信息\",db_connect)\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "df1.to_csv(\"外地文创部门部门信息表.csv\",index=False)\n",
    "\n",
    "\n",
    "#csv_data=pd.read_excel('工资汇总表.xlsx')\n",
    "#csv_data['发放日期'] = pd.to_datetime(csv_data['发放日期'])\n",
    "#depart_df = pd.read_excel('外地文创部门.xlsx')\n",
    "#depart_df.to_sql('外地文创部门',db_engine,if_exists='replace',index=False)\n",
    "#colum_df = depart_df.loc[depart_df['集团']].str.contains('元汇')\n",
    "#display(colum_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "07f930c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>Unnamed: 1</th>\n",
       "      <th>Unnamed: 2</th>\n",
       "      <th>Unnamed: 3</th>\n",
       "      <th>Unnamed: 4</th>\n",
       "      <th>Unnamed: 5</th>\n",
       "      <th>Unnamed: 6</th>\n",
       "      <th>账卡号</th>\n",
       "      <th>对手户名</th>\n",
       "      <th>交易次数</th>\n",
       "      <th>是否在库中</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07375001040010568</td>\n",
       "      <td>吉林省长白山天鼎土特产品有限公司</td>\n",
       "      <td>4</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07375001040014925</td>\n",
       "      <td>吉林省行健文化传媒有限公司</td>\n",
       "      <td>1</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07375001040014966</td>\n",
       "      <td>吉林省天鼎生态农业发展有限责任公司</td>\n",
       "      <td>3</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07375001040015047</td>\n",
       "      <td>安图县鼎佳智特产销售有限公司</td>\n",
       "      <td>1</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>07375001040015054</td>\n",
       "      <td>吉林省鼎利土特产品经销有限公司</td>\n",
       "      <td>3</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6228480178297571378</td>\n",
       "      <td>陈秀红</td>\n",
       "      <td>2</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6228480938272257677</td>\n",
       "      <td>樊毛林</td>\n",
       "      <td>1</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6230580000118691190</td>\n",
       "      <td>樊花</td>\n",
       "      <td>4</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6230583000005567567</td>\n",
       "      <td>李新光</td>\n",
       "      <td>6</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>91</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>6232086500018323245</td>\n",
       "      <td>樊露芳</td>\n",
       "      <td>7</td>\n",
       "      <td>是</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>92 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0  Unnamed: 1  Unnamed: 2  Unnamed: 3  Unnamed: 4  Unnamed: 5  \\\n",
       "0          NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "1          NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "2          NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "3          NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "4          NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "..         ...         ...         ...         ...         ...         ...   \n",
       "87         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "88         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "89         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "90         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "91         NaN         NaN         NaN         NaN         NaN         NaN   \n",
       "\n",
       "    Unnamed: 6                  账卡号               对手户名  交易次数 是否在库中  \n",
       "0          NaN    07375001040010568   吉林省长白山天鼎土特产品有限公司     4     是  \n",
       "1          NaN    07375001040014925      吉林省行健文化传媒有限公司     1     是  \n",
       "2          NaN    07375001040014966  吉林省天鼎生态农业发展有限责任公司     3     是  \n",
       "3          NaN    07375001040015047     安图县鼎佳智特产销售有限公司     1     是  \n",
       "4          NaN    07375001040015054    吉林省鼎利土特产品经销有限公司     3     是  \n",
       "..         ...                  ...                ...   ...   ...  \n",
       "87         NaN  6228480178297571378                陈秀红     2     是  \n",
       "88         NaN  6228480938272257677                樊毛林     1     是  \n",
       "89         NaN  6230580000118691190                 樊花     4     是  \n",
       "90         NaN  6230583000005567567                李新光     6     是  \n",
       "91         NaN  6232086500018323245                樊露芳     7     是  \n",
       "\n",
       "[92 rows x 11 columns]"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "import pandas as pd;\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "db_connect= db_engine.connect()\n",
    "depart_df = pd.read_excel('df_卡号信息.xlsx')\n",
    "depart_df.to_sql('df_卡号信息',db_engine,if_exists='replace',index=False)\n",
    "display(depart_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "370467fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd;\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "db_connect= db_engine.connect()\n",
    "df1 = pd.read_sql(\"select a.序号,a.姓名,a.身份证号码,a.银行账号,a.银行,a.实发工资,a.是否在库中,b.交易时间,b.交易金额,b.交易对手账卡号,b.对手户名,b.对手开户银行,b.摘要说明,b.备注 from 人员工资表 a INNER JOIN 合并交易明细信息 b on a.银行账号 = b.交易卡号 where a.`实发工资` = b.交易金额\"+ \n",
    "                  \"UNION SELECT * from 人员工资表 where 序号 not in (select 序号 from 人员工资临时表)\",db_connect)\n",
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd;\n",
    "from sqlalchemy import create_engine\n",
    "db_host = '192.168.1.102'\n",
    "db_user = 'root'\n",
    "db_password='admin'\n",
    "db_name = 'dk_information'\n",
    "db_engine = create_engine(f'mysql://{db_user}:{db_password}@{db_host}/{db_name}')\n",
    "db_connect= db_engine.connect()\n",
    "df1 = pd.read_sql(\"select * from 工资汇总表\",db_connect)\n",
    "df2 = pd.read_sql(\"select 交易卡号 from 合并交易明细信息 GROUP BY 交易卡号 \",db_connect)\n",
    "salary_df = df1.loc[:,['序号','姓名','身份证号码','银行账号','银行','实发工资']]\n",
    "salary_df['是否在库中'] = '否'\n",
    "#b.交易时间,b.交易金额,b.`交易对手账卡号`,b.对手户名,b.`对手开户银行`,b.摘要说明,b.备注\n",
    "salary_df['交易时间'] = ''\n",
    "salary_df['交易金额'] = ''\n",
    "salary_df['交易对手账卡号'] = ''\n",
    "salary_df['对手户名'] = ''\n",
    "salary_df['对手开户银行'] = ''\n",
    "salary_df['摘要说明'] = ''\n",
    "salary_df['备注'] = ''\n",
    "for idx,row in df2.iterrows():\n",
    "    #print(idx)\n",
    "    #print(row['交易卡号'])\n",
    "    card = row['交易卡号']\n",
    "    if  card in salary_df['银行账号'].values:\n",
    "        print(card)\n",
    "        rs_df = salary_df.loc[salary_df['银行账号'] == card]\n",
    "        index = rs_df.index\n",
    "        salary_df['是否在库中'].loc[index]  = '是'\n",
    "        print(salary_df.loc[index])\n",
    "salary_df.to_sql('人员工资表',db_engine,if_exists='replace',index=False)\n",
    "#display(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "32803232",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "5\n",
      "--------------------------------\n",
      "a b c\n"
     ]
    }
   ],
   "source": [
    "def foo(*args,**kwargs):\n",
    "    print(*args)\n",
    "    print('--------------------------------')\n",
    "    print(*kwargs)\n",
    "foo(5,a=5,b=6,c=7)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
